package com.backend.demo.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.EntityMode;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.DB2400Dialect;
import org.hibernate.dialect.DB2Dialect;
import org.hibernate.dialect.DerbyDialect;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.InformixDialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.Oracle8iDialect;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.engine.spi.Mapping;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.id.IdentifierGenerationException;
import org.hibernate.id.IdentifierGenerator;
import org.hibernate.mapping.ForeignKey;
import org.hibernate.mapping.Index;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.Table;
import org.hibernate.mapping.UniqueKey;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.persister.entity.AbstractEntityPersister;
import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.type.Type;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

import com.backend.demo.util.AutoadjustMultipleHiLoGeneratorHibernate36.IdInfo;



/**
 * hibernate 辅助工具类
 * 
 * @author ronald
 */
public class HibernateUtil {
	private static final AtomicFinalObject<Integer> dbMajorVersion = new AtomicFinalObject<Integer>();
	private static final Logger log = LoggerFactory.getLogger(HibernateUtil.class);
	/**
	 * 根据映射类和属性名得到数据库的字段名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @param fieldName
	 *            属性名
	 * @return
	 */
	public static String[] getColumnNames(SessionFactory sessionFactory,
			Class<?> clazz, String fieldName) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			return ep.getPropertyColumnNames(fieldName);
		}
		return null;
	}
	
	/**
	 * 根据映射类得到所有属性名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @param fieldName
	 *            属性名
	 * @return
	 */
	public static String[] getPropertyNames(SessionFactory sessionFactory,
			Class<?> clazz) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			return ep.getPropertyNames();
		}
		return null;
	}

	/**
	 * 根据映射类得到属性的类型
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @param fieldName
	 *            属性名
	 * @return
	 */
	public static Type getPropertyType(SessionFactory sessionFactory,
			Class<?> clazz,String propertyName) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			return ep.getPropertyType(propertyName);
		}
		return null;
	}
	
	/**
	 * 根据映射类和属性名得到数据库的字段名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @param fieldName
	 *            属性名
	 * @return
	 */
	public static String getColumnName(SessionFactory sessionFactory,
			Class<?> clazz, String fieldName) {
		String[] columnNames = getColumnNames(sessionFactory, clazz, fieldName);
		if (CollectionUtils.notEmpty(columnNames)) {
			return columnNames[0];
		}
		return null;
	}

	/**
	 * 根据映射类得到表名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 * @return
	 */
	public static String getTableName(SessionFactory sessionFactory,
			Class<?> clazz) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			return ep.getTableName();
		}
		return null;
	}

	/**
	 * 得到主键名字段名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @return 主键的字段名,如果为多个则表示为组合主键
	 */
	public static String[] getPKColumnNames(SessionFactory sessionFactory,
			Class<?> clazz) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			return ep.getKeyColumnNames();
		}

		return null;
	}

	/**
	 * 得到主键名字段名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @return 主键的字段名
	 */
	public static String getPKColumnName(SessionFactory sessionFactory,
			Class<?> clazz) {
		String[] names = getPKColumnNames(sessionFactory, clazz);
		if (CollectionUtils.notEmpty(names)) {
			return names[0];
		}
		return null;
	}

	/**
	 * 得到主键的属性名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @return
	 */
	public static String getPKFieldName(SessionFactory sessionFactory,
			Class<?> clazz) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep == null) {
			return null;
		}
		return ep.getIdentifierPropertyName();

	}

	/**
	 * 根据表名和字段名得到对方映射类中的属性名
	 * 
	 * @param sessionFactory
	 * @param tableName
	 *            表名
	 * @param columnName
	 *            字段名
	 * @return 对应的属性名
	 */
	public static String getFieldName(SessionFactory sessionFactory,
			String tableName, String columnName) {
		if (columnName == null) {
			return null;
		}
		AbstractEntityPersister ep = getEntityPersisterByTableName(
				sessionFactory, tableName);
		if (ep != null) {
			String[] names = ep.getPropertyNames();
			for (String n : names) {
				String[] name = ep.getPropertyColumnNames(n);
				if (CollectionUtils.notEmpty(name)) {
					if (name[0].equalsIgnoreCase(columnName)) {
						return n;
					}
				}
			}
		}
		return null;
	}

	/**
	 * 根据表名和字段名得到对方映射类中的属性名
	 * 
	 * @param sessionFactory
	 * @param clazz
	 *            映射类(DTO)
	 * @param columnName
	 *            字段名
	 * @return 对应的属性名
	 */
	public static String getFieldName(SessionFactory sessionFactory,
			Class<?> clazz, String columnName) {
		AbstractEntityPersister ep = getEntityPersisterByClassName(
				sessionFactory, clazz);
		if (ep != null) {
			String[] names = ep.getPropertyNames();
			for (String n : names) {
				String[] name = ep.getPropertyColumnNames(n);
				if (CollectionUtils.notEmpty(name)) {
					if (name[0].equalsIgnoreCase(columnName)) {
						return n;
					}
				}
			}
		}
		return null;
	}

	/**
	 * 根据表名得到映射类(DTO)
	 * 
	 * @param sessionFactory
	 * @param tableName
	 *            表名
	 * @return
	 */
	public static Class<?> getMappedClassByTableName(
			SessionFactory sessionFactory, String tableName) {
		AbstractEntityPersister ep = getEntityPersisterByTableName(
				sessionFactory, tableName);
		if (ep != null) {
			return ep.getMappedClass();
			// return ep.getName();
		}
		return null;
	}

	private static AbstractEntityPersister getEntityPersisterByClassName(
			SessionFactory sessionFactory, Class<?> clazz) {
		if (sessionFactory == null || clazz == null) {
			return null;
		}

		ClassMetadata meta = sessionFactory.getClassMetadata(clazz);
		if (meta == null) {
			return null;
		}
		if (meta instanceof AbstractEntityPersister) {
			return (AbstractEntityPersister) meta;
		}

		return null;

	}

	@SuppressWarnings("unchecked")
	private static AbstractEntityPersister getEntityPersisterByTableName(
			SessionFactory sessionFactory, String tableName) {
		if (sessionFactory == null || tableName == null) {
			return null;
		}
		Map<String, ClassMetadata> metas = sessionFactory.getAllClassMetadata();
		if (metas == null) {
			return null;
		}
		ClassMetadata meta = null;
		for (String key : metas.keySet()) {
			meta = metas.get(key);
			if (meta == null) {
				continue;
			}
			if (meta instanceof AbstractEntityPersister) {
				AbstractEntityPersister ep = (AbstractEntityPersister) meta;
				String tn = ep.getTableName();
				if (tn.equalsIgnoreCase(tableName)) {
					return ep;
				}
			}
		}

		return null;
	}

	/**
	 * 添加一个别名给主键生成器,像hilo就表示高低位
	 * 
	 * @param alias
	 *            别名
	 * @param clazz
	 *            ID生成器的实现类
	 */
	public static void addIdGeneratorAlias(String alias,
			Class<? extends IdentifierGenerator> clazz,Configuration config) {
		
//		addIdGeneratorAliasIn326(alias, clazz, config);
		config.getIdentifierGeneratorFactory().register(alias, clazz);
	}
	
	private static void addIdGeneratorAliasIn326(String alias,
			Class<? extends IdentifierGenerator> clazz,Configuration config){
		Field f = null;
		try {
			f = Class.forName("org.hibernate.id.IdentifierGeneratorFactory").getDeclaredField("GENERATORS");
		} catch (Exception e) {
			return;
		}

		if (f == null) {
			return;
		}

		HashMap map = (HashMap) ClassUtil.getFieldValue(null, f);
		if (map != null) {
			map.put(alias, clazz);
		}
	}
	
	private static void addIdGeneratorAliasIn353(String alias,
			Class<? extends IdentifierGenerator> clazz,Configuration config){
		if(config == null){
			return;
		}
		config.getIdentifierGeneratorFactory().register(alias, clazz);
	}
	
	public static void addIdGeneratorAlias(String alias,
			Class<? extends IdentifierGenerator> clazz) {
		addIdGeneratorAlias(alias, clazz,HibernateConfigurationHolder.getConfiguration());
	}
	
	/**
	 * 得到一个张表的create语句
	 * @param configuration
	 * @param sessionFactory
	 * @param dialect
	 * @param dtoClass
	 * @return
	 */
	public static NameString getCreateTableString(Configuration configuration, SessionFactory sessionFactory,Class<?> dtoClass){
		if(configuration == null || dtoClass == null){
			return null;
		}
		String defaultCatalog = Environment.getProperties().getProperty( Environment.DEFAULT_CATALOG );
		String defaultSchema = Environment.getProperties().getProperty( Environment.DEFAULT_SCHEMA );

		if(log.isDebugEnabled()){
			log.debug(String.format("尝试得到[{0}]的表创建语句,catalog=[{1}],schema=[{2}]",dtoClass.getName(),defaultCatalog,defaultSchema));
		}

		SessionFactoryImplementor sfi = null;
		if(sessionFactory instanceof SessionFactoryImplementor){
			sfi = (SessionFactoryImplementor) sessionFactory;
		}
		if(sfi == null){
			return null;
		}
		Dialect dialect = sfi.getDialect();
		
		Table table  = getTableByDTOClass(configuration, dtoClass);

		if(table == null){
			return null;
		}

		NameString nameString = new NameString();
		nameString.setName(table.getName());
		nameString.setValue(table.sqlCreateString(dialect, configuration.buildMapping(), defaultCatalog, defaultSchema));
		return nameString;
	}
	
	public static void executeSQL(SessionFactory sessionFactory,String sql){
		if(sessionFactory == null || sql == null){
			return;
		}
		
		Session sess = null;
		Transaction tx = null;
		try{
			sess = sessionFactory.openSession();
			tx = sess.beginTransaction();
			sess.createSQLQuery(sql).executeUpdate();
			tx.commit();
		} catch(Exception e){
			e.printStackTrace();
			tx.rollback();
			throw new RuntimeException(sql,e);
		} finally{
			closeSession(sess);
		}
	}
	
	public static NameString getDropTableString(Configuration configuration, SessionFactory sessionFactory,Class<?> dtoClass){
		if(configuration == null || dtoClass == null){
			return null;
		}
		String defaultCatalog = Environment.getProperties().getProperty( Environment.DEFAULT_CATALOG );
		String defaultSchema = Environment.getProperties().getProperty( Environment.DEFAULT_SCHEMA );

		if(log.isDebugEnabled()){
			log.debug(String.format("尝试得到[{0}]的表创建语句,catalog=[{1}],schema=[{2}]",dtoClass.getName(),defaultCatalog,defaultSchema));
		}

		SessionFactoryImplementor sfi = null;
		if(sessionFactory instanceof SessionFactoryImplementor){
			sfi = (SessionFactoryImplementor) sessionFactory;
		}
		if(sfi == null){
			return null;
		}
		Dialect dialect = sfi.getDialect();
		
		Table table  = getTableByDTOClass(configuration, dtoClass);

		if(table == null){
			return null;
		}

		NameString nameString = new NameString();
		nameString.setName(table.getName());
		nameString.setValue(table.sqlDropString(dialect, defaultCatalog, defaultSchema));
		return nameString;
	}
	
	
	
	public static Table getTableByDTOClass(Configuration configuration,Class<?> dtoClass){
		if(configuration == null || dtoClass == null){
			return null;
		}
		
		Iterator classMappings = configuration.getClassMappings();
		Table table  = null;

		while(classMappings.hasNext()){
			PersistentClass persistentClass = ((PersistentClass)classMappings.next());
			String name= persistentClass.getClassName();
			if(dtoClass.getName().equals(name)){
				table =	persistentClass.getTable();
				break;
			}
		}
		return table;
	}

	public static List<NameString> getCreateFKString(Configuration configuration, SessionFactory sessionFactory,Class<?> dtoClass){
		Table table = getTableByDTOClass(configuration, dtoClass);
		if(table == null){
			return null;
		}
		SessionFactoryImplementor sfi = null;
		if(sessionFactory instanceof SessionFactoryImplementor){
			sfi = (SessionFactoryImplementor) sessionFactory;
		}
		if(sfi == null){
			return null;
		}

		Iterator<ForeignKey> foreignKeyIterator = table.getForeignKeyIterator();
		if(foreignKeyIterator==null || !foreignKeyIterator.hasNext()){
			return null;
		}
		String defaultCatalog = Environment.getProperties().getProperty( Environment.DEFAULT_CATALOG );
		String defaultSchema = Environment.getProperties().getProperty( Environment.DEFAULT_SCHEMA );
		
		List<NameString> ret= new ArrayList<NameString>();
		while(foreignKeyIterator.hasNext()){
			NameString ns = new NameString(); 
			ForeignKey foreign = foreignKeyIterator.next();
			ns.setName(foreign.getName());
			ns.setValue(foreign.sqlCreateString(sfi.getDialect(), configuration.buildMapping(), defaultCatalog, defaultSchema));
			ret.add(ns);
		}
		
		return ret;
	}
	
	public static List<NameString> getCreateIndexString(Configuration configuration, SessionFactory sessionFactory,Class<?> dtoClass){
		Table table = getTableByDTOClass(configuration, dtoClass);
		if(table == null){
			return null;
		}
		
		SessionFactoryImplementor sfi = null;
		if(sessionFactory instanceof SessionFactoryImplementor){
			sfi = (SessionFactoryImplementor) sessionFactory;
		}
		if(sfi == null){
			return null;
		}

		Iterator<Index> indexIterator = table.getIndexIterator();
		if(indexIterator==null || !indexIterator.hasNext()){
			return null;
		}
		String defaultCatalog = Environment.getProperties().getProperty( Environment.DEFAULT_CATALOG );
		String defaultSchema = Environment.getProperties().getProperty( Environment.DEFAULT_SCHEMA );
		
		List<NameString> ret= new ArrayList<NameString>();
		while(indexIterator.hasNext()){
			NameString ns = new NameString(); 
			Index index = indexIterator.next();
			ns.setName(index.getName());
			ns.setValue(index.sqlCreateString(sfi.getDialect(), configuration.buildMapping(), defaultCatalog, defaultSchema));
			ret.add(ns);
		}
		
		return ret;
	}

	public static List<NameString> getCreateUniqueKeyString(Configuration configuration, SessionFactory sessionFactory,Class<?> dtoClass){
		Table table = getTableByDTOClass(configuration, dtoClass);
		if(table == null){
			return null;
		}
		
		SessionFactoryImplementor sfi = null;
		if(sessionFactory instanceof SessionFactoryImplementor){
			sfi = (SessionFactoryImplementor) sessionFactory;
		}
		if(sfi == null){
			return null;
		}

		Iterator<UniqueKey> indexIterator = table.getUniqueKeyIterator();
		if(indexIterator==null || !indexIterator.hasNext()){
			return null;
		}
		String defaultCatalog = Environment.getProperties().getProperty( Environment.DEFAULT_CATALOG );
		String defaultSchema = Environment.getProperties().getProperty( Environment.DEFAULT_SCHEMA );
		
		List<NameString> ret= new ArrayList<NameString>();
		while(indexIterator.hasNext()){
			NameString ns = new NameString(); 
			UniqueKey index = indexIterator.next();
			ns.setName(index.getName());
			ns.setValue(index.sqlCreateString(sfi.getDialect(), configuration.buildMapping(), defaultCatalog, defaultSchema));
			ret.add(ns);
		}
		
		return ret;
	}

	public static DbTableMetadata getTableMetadata(SessionFactoryImplementor sfi){
		log.debug("######  开始得到数据库表的所有字段信息");
		
		DbTableMetadata tm = new DbTableMetadata();
		String checkSQL = null;
		Dialect dialect = sfi.getDialect();

		// oracle 8,9i,10i
		if(dialect instanceof Oracle8iDialect){
			checkSQL = "SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS";
			// checkSQL = "SELECT col.TABLE_NAME,col.COLUMN_NAME,com.COMMENTS AS TABLE_COMMENT, col_com.COMMENTS as COL_COMMENT FROM USER_TAB_COLUMNS col INNER JOIN USER_TAB_COMMENTS com ON col.TABLE_NAME = com.TABLE_NAME INNER JOIN USER_COL_COMMENTS col_com ON col_com.TABLE_NAME = col.TABLE_NAME AND col_com.COLUMN_NAME = col.COLUMN_NAME";
		}
		
		// derby
		if(checkSQL==null && dialect instanceof DerbyDialect){
			checkSQL = "SELECT t1.TABLENAME,t2.COLUMNNAME FROM SYS.SYSTABLES t1,SYS.SYSCOLUMNS t2 WHERE t1.TABLEID = t2.REFERENCEID";
			// checkSQL = "SELECT t1.TABLENAME,t2.COLUMNNAME,'','' FROM SYS.SYSTABLES t1,SYS.SYSCOLUMNS t2 WHERE t1.TABLEID = t2.REFERENCEID";
		}

		//sqlserver2005
		int databaseMajorVersion = getDatabaseMajorVersion(sfi);
		
		if(checkSQL==null && dialect instanceof SQLServerDialect && databaseMajorVersion == 9){
			checkSQL = "SELECT CAST(C.NAME as varchar) AS 'tableName',CAST(A.NAME as varchar) AS 'columnName' FROM SYSOBJECTS AS C LEFT JOIN SYSCOLUMNS AS A ON C.ID = A.ID WHERE C.type='U'";
		}
		
		//sqlserver2000
		if(checkSQL==null && dialect instanceof SQLServerDialect && databaseMajorVersion == 8){
			checkSQL = "SELECT CAST(C.NAME as varchar) AS 'tableName',CAST(A.NAME as varchar) AS 'columnName' FROM SYSOBJECTS AS C LEFT JOIN SYSCOLUMNS AS A ON C.ID = A.ID WHERE C.type='U'";
		}
		
		//mysql 
		if(checkSQL==null && dialect instanceof MySQLDialect){
			checkSQL = "SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '"+getSchema(sfi)+"'";
			// checkSQL = "SELECT tab.TABLE_NAME,col.COLUMN_NAME,tab.TABLE_COMMENT,col.COLUMN_COMMENT FROM INFORMATION_SCHEMA.TABLES tab INNER JOIN INFORMATION_SCHEMA.COLUMNS col ON col.TABLE_NAME = tab.TABLE_NAME";
		}

		// as400 db2
		if(checkSQL==null && dialect instanceof DB2400Dialect){
			checkSQL = "SELECT t1.TABLE_NAME,t2.COLUMN_NAME FROM SYSTABLES t1,SYSCOLUMNS t2 WHERE t1.TABLE_NAME = t2.TABLE_NAME";
		}

		// db2 FOR LUW
		if(checkSQL==null && dialect instanceof DB2Dialect){
			checkSQL = "SELECT T1.NAME AS TABLE_NAME,T2.NAME AS COLUMN_NAME FROM SYSIBM.SYSTABLES T1,SYSIBM.SYSCOLUMNS T2 WHERE T1.NAME = T2.TBNAME";
		}

		Session session = sfi.openSession();
		
		try{
			// hibernate sql
			SQLQuery createSQLQuery = session.createSQLQuery(checkSQL);
			List<?> list = createSQLQuery.list();
			if(list != null && list.size() > 0){
				for(Object obj : list){
					Object[] objs = (Object[]) obj;
					tm.addColumn(String.valueOf(objs[0]), String.valueOf(objs[1]));
				}
			}
		} finally {
			HibernateUtil.closeSession(session);
		}
		
		log.debug("######  结束得到数据库表的所有字段信息");
		return tm;
	}
	
	public static boolean isTableExists(SessionFactoryImplementor sfi,String tableName){
		String checkSQL = null;
		Dialect dialect = sfi.getDialect();
		
		// oracle 8,9i,10i
		if(dialect instanceof Oracle8iDialect){
			checkSQL = "SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '%s'";
			checkSQL =String.format(checkSQL, tableName);
		}
		
		//sqlserver2005
		int databaseMajorVersion = getDatabaseMajorVersion(sfi);
		
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 9){
			checkSQL = "SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID('[%s]') AND type ='U'";
			checkSQL =String.format(checkSQL,tableName);
		}
		
		//sqlserver2000
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 8){
			checkSQL = "SELECT COUNT(*) FROM sysobjects WHERE name = '%s' AND type = 'U'";
			checkSQL =String.format(checkSQL,tableName);
		}
		
		//mysql 
		if(dialect instanceof MySQLDialect){
			checkSQL = "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_NAME = '%s' AND TABLE_SCHEMA = '%s'";
			checkSQL =String.format(checkSQL, tableName,getSchema(sfi));
		}

		// derby
		if(checkSQL==null && dialect instanceof DerbyDialect){
			checkSQL = "SELECT COUNT(*) FROM SYS.SYSTABLES WHERE TABLENAME = '%s'";
			checkSQL =String.format(checkSQL, tableName);
		}

		// as400 db2
		if(checkSQL==null && dialect instanceof DB2400Dialect){
			checkSQL = "SELECT COUNT(*) FROM SYSTABLES WHERE TABLE_NAME = '%s'";
			checkSQL =String.format(checkSQL, tableName);
		}
		
		//db2
		if(checkSQL==null && dialect instanceof DB2Dialect){
			checkSQL = "SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME = '%s' AND TYPE = 'T'";
			checkSQL =String.format(checkSQL, tableName);
		}

		//informix
		if(checkSQL==null && dialect instanceof InformixDialect){
			checkSQL = "SELECT COUNT(*) FROM SYSTABLES WHERE TABNAME = '%s'";
			checkSQL =String.format(checkSQL, tableName);
		}
		
		Session session = sfi.openSession();
		
		try{
			SQLQuery createSQLQuery = session.createSQLQuery(checkSQL);
			if(((Number)createSQLQuery.list().get(0)).intValue()>0){
				return true;
			}
		}catch(Exception e){
			return false;
		}
		finally{
			HibernateUtil.closeSession(session);
		}
		return false;	
	}

	public static boolean isViewExists(SessionFactoryImplementor sfi,String viewName){
		String checkSQL = null;
		Dialect dialect = sfi.getDialect();
		
		// oracle 8,9i,10i
		if(dialect instanceof Oracle8iDialect){
			checkSQL = "SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}
		
		//sqlserver2005
		int databaseMajorVersion = getDatabaseMajorVersion(sfi);
		
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 9){
			checkSQL = "SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID('[%s]') AND type ='V'";
			checkSQL =String.format(checkSQL,viewName);
		}
		
		//sqlserver2000
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 8){
			checkSQL = "SELECT COUNT(*) FROM sysobjects WHERE name = '%s' AND type = 'V'";
			checkSQL =String.format(checkSQL,viewName);
		}
		
		//mysql 
		if(dialect instanceof MySQLDialect){
			checkSQL = "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_NAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}

		// derby
		if(checkSQL==null && dialect instanceof DerbyDialect){
			checkSQL = "SELECT COUNT(*) FROM SYS.SYSTABLES WHERE TABLENAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}

		// as400 db2
		if(dialect instanceof DB2400Dialect){
			checkSQL = "SELECT COUNT(*) FROM SYSTABLES WHERE TABLE_NAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}
		
		//db2
		if(checkSQL==null && dialect instanceof DB2Dialect){
			checkSQL = "SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE NAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}

		//informix
		if(checkSQL==null && dialect instanceof InformixDialect){
			checkSQL = "SELECT COUNT(*) FROM SYSTABLES WHERE TABNAME = '%s'";
			checkSQL =String.format(checkSQL, viewName);
		}
		
		Session session = sfi.openSession();
		
		try{
			SQLQuery createSQLQuery = session.createSQLQuery(checkSQL);
			if(((Number)createSQLQuery.list().get(0)).intValue()>0){
				return true;
			}
		}finally{
			HibernateUtil.closeSession(session);
		}
		return false;	
	}

	public static boolean isIndexExists(SessionFactoryImplementor sfi,String tableName,String indexName){
		String checkSQL = null;
		Dialect dialect = sfi.getDialect();
		
		// oracle 8,9i,10i
		if(dialect instanceof Oracle8iDialect){
			checkSQL = "SELECT count(*) FROM user_objects WHERE object_name = '%s' and object_type='INDEX'";
			checkSQL =String.format(checkSQL, indexName);
		}

		//sqlserver2005
		int databaseMajorVersion = getDatabaseMajorVersion(sfi);
		
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 9){
			checkSQL = "select count(*) from sysindexes,sysobjects where sysindexes.id = sysobjects.id and sysobjects.name = '%s' AND sysindexes.name = '%s'";
			checkSQL =String.format(checkSQL,indexName,tableName);
		}
		
		//sqlserver2000
		if(dialect instanceof SQLServerDialect && databaseMajorVersion == 8){
			checkSQL = "select count(*) from sysindexes,sysobjects where sysindexes.id = sysobjects.id and sysobjects.name = '%s' AND sysindexes.name = '%s'";
			checkSQL =String.format(checkSQL,tableName,indexName);
		}
		
		//mysql 
		if(dialect instanceof MySQLDialect){
			checkSQL = "SELECT count(*) FROM information_schema.STATISTICS WHERE TABLE_NAME = '%s' and INDEX_NAME='%s'";
			checkSQL =String.format(checkSQL, tableName,indexName);
		}

		// derby
		if(checkSQL==null && dialect instanceof DerbyDialect){
			checkSQL = "SELECT count(*) FROM SYS.SYSCONGLOMERATES b,SYS.SYSTABLES a where a.tableid = b.tableid and a.tablename='%s' and b.CONGLOMERATENAME = '%s'";
			checkSQL =String.format(checkSQL, tableName, indexName);
		}

		// as400 db2
		if(checkSQL == null && dialect instanceof DB2400Dialect){
			checkSQL = "SELECT count(*) FROM SYSINDEXES WHERE TABLE_NAME = '%s' AND INDEX_NAME = '%s'";
			checkSQL = String.format(checkSQL, tableName , indexName);
		}
		
		//db2
		if(checkSQL==null && dialect instanceof DB2Dialect){
			checkSQL = "SELECT count(*) FROM SYSIBM.SYSINDEXES WHERE TBNAME = '%s' AND NAME = '%s'";
			checkSQL = String.format(checkSQL, tableName , indexName);
		}

		//informix
		if(checkSQL==null && dialect instanceof InformixDialect){
			checkSQL = "select count(*) sysindexes,systables where sysindexes.tabid = systables.tabid AND systables.tabname = '%s' AND sysindexes.idxname = '%s'"; 
			checkSQL = String.format(checkSQL, tableName , indexName);
		}
		
		Session session = sfi.openSession();
		
		try{
			SQLQuery createSQLQuery = session.createSQLQuery(checkSQL);
			if(((Number)createSQLQuery.list().get(0)).intValue()>0){
				return true;
			}
		}finally{
			HibernateUtil.closeSession(session);
		}
		return false;	
	}

	private static int getDatabaseMajorVersion(SessionFactoryImplementor sfi){
		if(dbMajorVersion.get()!=null){
			return dbMajorVersion.get();
		}
		Connection connection = null;
		try {
			connection = sfi.getConnectionProvider().getConnection();
			DatabaseMetaData md = connection.getMetaData();
			if(md.getDatabaseProductVersion().contains("8.00")){
				dbMajorVersion.set(8);
				return 8;
			}
			
			if(md.getDatabaseProductVersion().contains("9.00")){
				dbMajorVersion.set(9);
				return 9;
			}

			return 9;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}finally{
			DBUtil.closeConnection(connection);
		}
	}
	
	private static String getSchema(SessionFactoryImplementor sfi){
		Connection connection = null;
		 try {
			connection = sfi.getConnectionProvider().getConnection();
			return connection.getCatalog();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeConnection(connection);
		}
		return null;
	}
	
	/**
	 * 根据 SessionFactory得到数据库的方言
	 * 
	 * @param sessionFactory
	 * @return
	 */
	public static Dialect getDialect(SessionFactory sessionFactory){
		if(sessionFactory == null){
			return null;
		}
		
		if(sessionFactory instanceof SessionFactoryImplementor){
			return ((SessionFactoryImplementor)sessionFactory).getDialect();
		}
		return null;
	}
	
	public static void closeSession(Session session){
		SessionFactoryUtils.closeSession(session);
	}
	
	public static String getSelectMaxSQL(SessionFactory sessionFactory,Class<?> clazz){
		String tableName = getTableName(sessionFactory, clazz);
		String pkName = getPKColumnName(sessionFactory, clazz);
		
		String sql = "SELECT MAX( {0} ) FROM {1}";
		return MessageFormat.format(sql,pkName,tableName);
	}
	
	public static boolean isMutable(SessionFactory sessionFactory,Class<?> clazz){
		return getEntityPersisterByClassName(sessionFactory, clazz).isMutable();
	}
	
	public static boolean isView(SessionFactory sessionFactory,Class<?> clazz){
		boolean mutable = getEntityPersisterByClassName(sessionFactory, clazz).isMutable();
		if(!mutable){
			return true;
		}
		
		if(getTableName(sessionFactory,clazz).toLowerCase().startsWith("v")){
			return true;
		}
		return false;
	}
	
	public static void initAllIdValue(SessionFactory sessionFactory){
		String pkColumnName = AutoadjustMultipleHiLoGenerator.DEFAULT_PK_COLUMN;
		String valueColumnName = AutoadjustMultipleHiLoGenerator.DEFAULT_VALUE_COLUMN;
		String table = AutoadjustMultipleHiLoGenerator.DEFAULT_TABLE;
		initAllIdValue(sessionFactory, table, pkColumnName, valueColumnName);
	}
	
	public static void initAllIdValue(SessionFactory sessionFactory,String tableName,String seqName,String seqValueName){
		Map<String,ClassMetadata> map = sessionFactory.getAllClassMetadata();
		Session sess = sessionFactory.openSession();
		String pkColumnName = seqName;
		String valueColumnName = seqValueName;
		String table = tableName;

		try{
			for(String key:map.keySet()){
				Class<?> clazz = null;
				try {
					clazz = Class.forName(key);
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
				
				// 如果是视图,不处理
				if(isView(sessionFactory,clazz)){
					continue;
				}
	
				// 如果不是htf的主键生成器,不处理
//				if(!isHtf(sessionFactory,clazz)){
//					continue;
//				}
				String _tableName = getTableName(sessionFactory, clazz);
				IdInfo idInfo = AutoadjustMultipleHiLoGeneratorHibernate36.getIdInfo(_tableName);
				if(idInfo == null){
					continue;
				}
				
				int maxLo = idInfo.getMaxLo();
				
//				String sql = "select {2} from {0} where {1} = ?";
//				sql = MessageFormat.format(sql, table,pkColumnName,valueColumnName);
				
				String insert = "insert into %s (%s,%s) values('%s', ?)";
				insert = String.format(insert, table,pkColumnName,valueColumnName,_tableName);

				String update = "update %s set %s = ? where %s = '%s'";
				update = String.format(update, table,valueColumnName,pkColumnName,_tableName);
				
//				SQLQuery sqlQuery = sess.createSQLQuery(sql);
//				sqlQuery.setString(0, tableName);
				
				Object nowValue = getHibernateSeqNowValue(_tableName,sess,tableName,seqName,seqValueName);
				if(nowValue == null){
					// insert
					try{
						String sqlMax = getSelectMaxSQL(sessionFactory, clazz);
						int result = 0;
						Object ret = sess.createSQLQuery(sqlMax).uniqueResult();
						if(ret == null){
							result = 0;
						} else {
							int curVal = ((Number)ret).intValue();
							int tmp = curVal / (maxLo + 1);
							result = tmp + 1;
						}
						
						SQLQuery insertQuery = sess.createSQLQuery(insert);
						insertQuery.setLong(0, result);
						insertQuery.executeUpdate();
					}catch(Exception e){
						continue;
					}
				}else{
					// update 
					try{
						String sqlMax = getSelectMaxSQL(sessionFactory, clazz);
						int result = 0;
						Object ret = sess.createSQLQuery(sqlMax).uniqueResult();
						if(ret == null){
							result = 0;
						} else {
							int curVal = ((Number)ret).intValue();
							int tmp = curVal / (maxLo + 1);
							result = tmp + 1;
						}
						
						if(((Number)nowValue).intValue() < result){
							SQLQuery updateQuery = sess.createSQLQuery(update);
							updateQuery.setLong(0, result);
							updateQuery.executeUpdate();
						}
					}catch(Exception e){
						continue;
					}
				}
			}
		}finally{
			HibernateUtil.closeSession(sess);
		}
	}
	
	public static void initAllIdValueWhenUseProduce(SessionFactory sessionFactory){
		Map<String,ClassMetadata> map = sessionFactory.getAllClassMetadata();
		Session sess = sessionFactory.openSession();
		String pkColumnName = "SEQ_TABLE_NAME";
		String valueColumnName = "SEQ_NEXT_VALUE";
		String table = "ID_SEQ";

		try{
			for(String key:map.keySet()){
				Class<?> clazz = null;
				try {
					clazz = Class.forName(key);
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
				
				// 如果是视图,不处理
				if(isView(sessionFactory,clazz)){
					continue;
				}
	
				String _tableName = getTableName(sessionFactory, clazz);
				
//				String insert = "insert into %s (%s,%s) values('%s', ?)";
//				insert = String.format(insert, table,pkColumnName,valueColumnName,_tableName);

				String update = "update %s set %s = ? where %s = '%s'";
				update = String.format(update, table,valueColumnName,pkColumnName,_tableName);
				
				Object nowValueInIdSeq = getHibernateSeqNowValue(_tableName,sess,table,pkColumnName,valueColumnName);
				if(nowValueInIdSeq == null){
					// insert
//					try{
//						String sqlMax = getSelectMaxSQL(sessionFactory, clazz);
//						int resultInDb = 1;
//						Object ret = sess.createSQLQuery(sqlMax).uniqueResult();
//						if(ret == null){
//							resultInDb = 1;
//						} else {
//							int curVal = ((Number)ret).intValue();
//							resultInDb = curVal + 1;
//						}
//						
//						SQLQuery insertQuery = sess.createSQLQuery(insert);
//						insertQuery.setLong(0, resultInDb);
//						insertQuery.executeUpdate();
//					}catch(Exception e){
//						continue;
//					}
				}else{
					// update 
					try{
						String sqlMax = getSelectMaxSQL(sessionFactory, clazz);
						int resultInDb = 1;
						Object ret = sess.createSQLQuery(sqlMax).uniqueResult();
						if(ret == null){
							resultInDb = 1;
						} else {
							int curVal = ((Number)ret).intValue();
							resultInDb = curVal + 1;
						}
						
						if(((Number)nowValueInIdSeq).intValue() <= resultInDb){
							SQLQuery updateQuery = sess.createSQLQuery(update);
							updateQuery.setLong(0, resultInDb);
							updateQuery.executeUpdate();
						}
					}catch(Exception e){
						continue;
					}
				}
			}
		}finally{
			HibernateUtil.closeSession(sess);
		}
	}

	public static List<Object[]> nameValues = null;
	private static synchronized Object getHibernateSeqNowValue(String tableName,Session session){
		if(nameValues == null){
			String pkColumnName = AutoadjustMultipleHiLoGenerator.DEFAULT_PK_COLUMN;
			String valueColumnName = AutoadjustMultipleHiLoGenerator.DEFAULT_VALUE_COLUMN;
			String table = AutoadjustMultipleHiLoGenerator.DEFAULT_TABLE;
			String sql = "select {1},{2} from {0}";
			sql = MessageFormat.format(sql,table,pkColumnName,valueColumnName);
			nameValues = (List<Object[]>)session.createSQLQuery(sql).list();
		}
		
		if(nameValues == null){
			return null;
		}
		
		for(Object[] objs : nameValues){
			if(tableName.equals(objs[0])){
				return objs[1];
			}
		}
		
		return null;
	}
	
	private static synchronized Object getHibernateSeqNowValue(String tableName,Session session,String table,String pkColumnName,String valueColumnName){
		if(nameValues == null){
			String sql = "select {1},{2} from {0}";
			sql = MessageFormat.format(sql,table,pkColumnName,valueColumnName);
			nameValues = (List<Object[]>)session.createSQLQuery(sql).list();
		}
		
		if(nameValues == null){
			return null;
		}
		
		for(Object[] objs : nameValues){
			if(tableName.equals(objs[0])){
				return objs[1];
			}
		}
		
		return null;
	}
	
	private static boolean isHtf(SessionFactory sessionFactory,Class<?> clazz){
		AbstractEntityPersister persis = getEntityPersisterByClassName(sessionFactory, clazz);
		return persis.getIdentifierGenerator().getClass() == AutoadjustMultipleHiLoGenerator.class;
	}
	
	public static String printAllSQL(SessionFactory sessionFactory){
		Collection<ClassMetadata> list = (Collection<ClassMetadata>) sessionFactory.getAllClassMetadata().values();
		List<String> lst = new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		for(ClassMetadata md : list){
			lst.add(getCreateTableString(HibernateConfigurationHolder.getConfiguration(), sessionFactory, md.getMappedClass()).toString());
		}
		Collections.sort(lst);
		for(String s : lst){
			sb.append(s);
			sb.append("\n");
		}
		return sb.toString();
	}
	
	public static Number createNumber(long value, Class clazz) throws IdentifierGenerationException {
		if ( clazz == Long.class ) {
			return new Long( value );
		}
		else if ( clazz == Integer.class ) {
			return new Integer( ( int ) value );
		}
		else if ( clazz == Short.class ) {
			return new Short( ( short ) value );
		}
		else {
			throw new IdentifierGenerationException( "unrecognized id type : " + clazz.getName() );
		}
	}
}
